import processing.core.*;

import java.sql.Timestamp;
import java.util.List;
import java.sql.*;

public class RatingChart extends PApplet implements TLModule{
    int SCREEN_SIZE_X = 780;
    int SCREEN_SIZE_Y = 210;

	int changed = 1;
    int aantalRows;
    int positie;
    int[][] solchart = new int[100][3];
    
    ResultSet rs;
	// Connectie maken met de database
    Connection connection;
    
	//public static void main(String args[]){
	public RatingChart(){
    	// Runnen als Java applet ipv java applicatie
    	// PApplet.main(new String[] { "--present", "RatingChart" });
    	
    	try {
        	// Load the JDBC driver
        	String driverName = "org.gjt.mm.mysql.Driver"; // MySQL MM JDBC driver 
    
        	Class.forName(driverName);

        	// Create a connection to the database
        	String serverName = "localhost";
        	String mydatabase = "infviz";
        	String url = "jdbc:mysql://" + serverName + "/" + mydatabase; // a JDBC url
        	String username = "root";
        	String password = "augmented";
        	
        	// maak connectie
        	connection = DriverManager.getConnection(url, username, password);
        	System.out.println("Connected to the database");

		  //System.out.println("Connection closed");	
        	//connection.close();
        } 

        catch (ClassNotFoundException e){
        	// Could not find the database driver
        	System.out.println("Could not find the database driver");
        	System.err.println(e.getMessage());
        } 

        catch (SQLException e){
        	// Could not connect to the database
        	System.out.println("Could nog connect to the database");
        	System.err.println(e.getMessage());
        }
   	}
		
	public void setup() {
	  background(255);
	  size(SCREEN_SIZE_X,SCREEN_SIZE_Y);
	  PFont font = loadFont("ScalaSans-Caps-32.vlw");
	  textFont(font, 14);

	  try {		  
		  String query = "SELECT song_id, t2.* FROM song join(SELECT @rownum:=@rownum+1 'twitterrank', t.* FROM (SELECT ranking chartrank, song_title, SUM(nr_tweets) nr_tweets FROM ranking LEFT JOIN song_popularity USING (song_title) WHERE chart_name = 'hot-100' AND chart_date = '2010-05-01' AND DAYOFYEAR(date) > DAYOFYEAR(chart_date)-4 AND DAYOFYEAR(date) < DAYOFYEAR(chart_date) + 4 GROUP BY song_title ORDER BY nr_tweets desc) t, (SELECT @rownum:=0) s)t2 using (song_title) order by chartrank;";
		  Statement stmt = connection.createStatement();
		  rs = stmt.executeQuery(query);

		  //aantal rows uitzoeken
		  int i = 0;
		  while(rs.next()){
			  solchart[i][0] = i;
			  solchart[i][1] = rs.getInt("song_id");
				
			  int twrank = rs.getInt("twitterrank");
			  int chrank = rs.getInt("chartrank");
			  int chartranking = chrank-twrank;			
				
			  solchart[i][2] = chartranking;
			  i++;
		  }
		  aantalRows = i;
		  changed = 1;
	  }
	  catch (SQLException e) {
	  // could not connect to the database
		  System.out.println("could not connect to the database");
	  }
	  	  
	}
	
	public void draw(){
		if (changed == 1){
			positie = 22;
			stroke(0,173,239);  						//blauwe omlijning voor de bars
			fill(0,173,239);    						//blauwe filling voor de bars
	   		
		    for (int i=0; i<aantalRows; i++){
		      rect(positie,107,6,solchart[i][2]);      //teken bars
		      positie = positie + 7;				    //schuif de 'potlood' op voor et tekenen van de volgende bar
		    }
		    drawAxisAndLegend();
		    changed = 0;								//rest boolean 
	  }
	}

	public void drawAxisAndLegend(){
	    stroke(0);          						//zwarte omlijning voor de Y-as
	    line(20,20,20,190); 						//X-as
    
	    stroke(255,191,1);  						//gele omlijning voor de Y-as
	    fill(255,191,1);    						//gele filling voor de Y-as
	    rect(21,105,650,2); 						//Y-as 695

	    //gele circle
	    stroke(255,191,1);  						//gele omlijning voor de circle
	    fill(255,191,1);    						//gele filling voor de circle
	    ellipseMode(CENTER);
	    ellipse(675,16,10,10);
	    
	    //tekst
	    fill(0);
	    textAlign(LEFT);
	    text("Billboard", 685, 13);
	    text("Ratings", 685, 27);

	    //blauwe circle
	    stroke(0,173,239);  						//blauwe omlijning voor de circle
	    fill(0,173,239);    						//blauwe filling voor de circle
	    ellipseMode(CENTER);
	    ellipse(675,48,10,10);
	    
	    //tekst
	    fill(0);
	    textAlign(LEFT);
	    text("Twitter", 685, 45);
	    text("Ratings", 685, 59);

	    //as typen
	    fill(0);
	    textAlign(LEFT);
	    text("Songs", 685, 102);
	    text("1-100", 685, 117);
	}
	
	@Override
	public void highlightPhrase(List<Long> phraseId) {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void highlightSong(List<Long> songId) {
		Long temp;

		for (int j=0; j<songId.size(); j++){
			temp = songId.get(j);
		    for (int i=0; i<aantalRows; i++){
			      if(solchart[i][1]==temp){
			  		positie = 22 +(6*solchart[i][0]);		//22 is de x-as + 6(de breedte van de bars)*de plaatsing in de top 100
					stroke(255,191,1);					  						//gele omlijning bars
					fill(255,191,1);    											//gele filling bars
					rect(positie,107,10,solchart[i][2]);                          //teken bars
			      }
			    }
		}
	}

	@Override
	public void highlightUser(List<String> users) {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void reset() {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void resetPhrase() {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void resetSong() {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void resetTime() {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void resetUser() {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void setPhrase(List<Long> phraseId) {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void setSong(List<Long> songId) {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void setTimeSpan(Timestamp start, Timestamp end) {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void setUser(List<String> users) {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void resetBillBoard(String chartName) {
		// TODO Auto-generated method stub
		
	}

	@Override
	public void setBillBoard(String chartName) {
		// TODO Auto-generated method stub
		
	}

//	@Override
	public void setChart(String chartname) {
		try {
			String query = "SELECT song_id, t2.* FROM song join(SELECT @rownum:=@rownum+1 'twitterrank', t.* FROM (SELECT ranking chartrank, song_title, SUM(nr_tweets) nr_tweets FROM ranking LEFT JOIN song_popularity USING (song_title) WHERE chart_name = '" + chartname + "' AND chart_date = '2010-05-01' AND DAYOFYEAR(date) > DAYOFYEAR(chart_date)-4 AND DAYOFYEAR(date) < DAYOFYEAR(chart_date) + 4 GROUP BY song_title ORDER BY nr_tweets desc) t, (SELECT @rownum:=0) s)t2 using (song_title) order by chartrank;";
			Statement stmt = connection.createStatement();
			rs = stmt.executeQuery(query);

			//aantal rows uitzoeken
			int i = 0;
			while(rs.next()){
				solchart[i][0] = i;
				solchart[i][1] = rs.getInt("song_id");
					
				int twrank = rs.getInt("twitterrank");
				int chrank = rs.getInt("chartrank");
				int chartranking = chrank-twrank ;			
					
				solchart[i][2] = chartranking;
				i++;
			}
			aantalRows = i;
			changed = 1;
		}
		  catch (SQLException e) {
		  // could not connect to the database
			  System.out.println("could not connect to the database");
		  }
		
		
	}

//	@Override
	public void setTime(String time) {
		try {
			String query = "SELECT song_id, t2.* FROM song join(SELECT @rownum:=@rownum+1 'twitterrank', t.* FROM (SELECT ranking chartrank, song_title, SUM(nr_tweets) nr_tweets FROM ranking LEFT JOIN song_popularity USING (song_title) WHERE chart_name = 'hot-100' AND chart_date = '" + time + "' AND DAYOFYEAR(date) > DAYOFYEAR(chart_date)-4 AND DAYOFYEAR(date) < DAYOFYEAR(chart_date) + 4 GROUP BY song_title ORDER BY nr_tweets desc) t, (SELECT @rownum:=0) s)t2 using (song_title) order by chartrank;";
			Statement stmt = connection.createStatement();
			rs = stmt.executeQuery(query);

			//aantal rows uitzoeken
			int i = 0;
			while(rs.next()){
				solchart[i][0] = i;
				solchart[i][1] = rs.getInt("song_id");
					
				int twrank = rs.getInt("twitterrank");
				int chrank = rs.getInt("chartrank");
				int chartranking = chrank-twrank ;			
					
				solchart[i][2] = chartranking;
				i++;
			}
			aantalRows = i;
			changed = 1;
		}
		  catch (SQLException e) {
		  // could not connect to the database
			  System.out.println("could not connect to the database");
		  }
	}
}